USE [NEPALICORNER]
GO

--------------------------------------------------------------------------------
--Categories
--------------------------------------------------------------------------------
BEGIN
	DECLARE @CategoryName VARCHAR(20);
   IF NOT EXISTS (SELECT CategoryName FROM Categories 
                   WHERE CategoryName in 
				   ('Furniture',
					'Vehicles',
					'Electronics',
					'Jobs'))
   BEGIN
       INSERT INTO [Categories] ([CategoryName])
       VALUES
		('Furniture'),
		('Vehicles'),
		('Electronics'),
		('Jobs')
	END
END
GO

--------------------------------------------------------------------------------
-- Countries 
--------------------------------------------------------------------------------
BEGIN
	DECLARE @CountryAbbr VARCHAR(20);
	IF NOT EXISTS (SELECT CountryAbbr FROM Countries 
                   WHERE CountryAbbr in 
				   ('USA',
					'CA'))
	BEGIN
	INSERT INTO [dbo].[Countries]
           ([CountryName],[CountryAbbr])
     VALUES
           ('United States', 'USA'),
		   ('Canada', 'CA')
	END
END
GO

--------------------------------------------------------------------------------
-- States
--------------------------------------------------------------------------------
BEGIN
	DECLARE @StateAbbr VARCHAR(20);
	IF NOT EXISTS (SELECT StateAbbr FROM States 
                   WHERE StateAbbr in 
				   ('KS','AL','TX','AB','ON'))
	BEGIN
	INSERT INTO [dbo].[States]
           ([StateName],[StateAbbr],[CountryId])     
		   (
			select 'Texas', 'TX', Id from Countries where CountryAbbr = 'USA'
			UNION ALL
			select 'Kansas', 'KS', Id from Countries where CountryAbbr = 'USA'
			UNION ALL
			select 'Alabama', 'AL', Id from Countries where CountryAbbr = 'USA'
			UNION ALL
			select 'Ontario', 'ON', Id from Countries where CountryAbbr = 'CA'
			UNION ALL
			select 'Alberta', 'AB', Id from Countries where CountryAbbr = 'CA'
			)
	END
END
GO
--------------------------------------------------------------------------------
-- Cities
--------------------------------------------------------------------------------
BEGIN
	DECLARE @CityName VARCHAR(20);
	IF NOT EXISTS (SELECT CityName FROM Cities 
                   WHERE CityName in 
				   ('Overland Park','Alabama City','Dallas','Alberta City','Ontario City'))
	BEGIN
	INSERT INTO [dbo].[Cities]
           ([CityName],[StateId])
		   (
			select 'Alabama City', Id from States where StateAbbr = 'AL'
			UNION ALL
			select 'Ontario City', Id from States where StateAbbr = 'ON'
			UNION ALL
			select 'Dallas', Id from States where StateAbbr = 'TX'
			UNION ALL
			select 'Overland Park', Id from States where StateAbbr = 'KS'
			UNION ALL
			select 'Alberta City', Id from States where StateAbbr = 'AB'
			)
	END
END
GO